import sys, os, re
import sqlite3 as dbapi

class SQL():

  def __init__(self):
    if os.path.exists("links.dat") == True:
      self.bbdd = dbapi.connect("links.dat")
      self.cursor = self.bbdd.cursor()
    else:
      self.bbdd = dbapi.connect("links.dat")
      self.cursor = self.bbdd.cursor()
      self.initdatabase()

  def initdatabase(self):
    """
      Links:
        lock = 0; Ready to download
        lock = 1; Downloading
        lock = 2; Failed
        lock = 3: Paused
        lock = 4: Download
      config:
        cdn = BOOL
        cdn_path = CDN PATH
    """
    self.cursor.execute("""create table links (link text, filename text,priority int, lock int)""")
    self.cursor.execute("""create table logs (fecha datetime, desc text)""")
    self.cursor.execute("""create table config (parameter text, value text)""")
    self.cursor.execute("""insert into config values('theme','default')""")
    self.cursor.execute("""insert into config values('concurrency',4)""")
    self.cursor.execute("""insert into config values('download_limit','')""")
    self.cursor.execute("""insert into config values('dest_path','.')""")
    self.cursor.execute("""insert into config values('mu_user','')""")
    self.cursor.execute("""insert into config values('mu_pwd','')""")
    self.cursor.execute("""insert into config values('mu_cookie','')""")
    self.cursor.execute("""insert into config values('rs_user','')""")
    self.cursor.execute("""insert into config values('rs_pwd','')""")
    self.cursor.execute("""insert into config values('cdn','0')""")
    self.cursor.execute("""insert into config values('cdn_path','')""")
    self.bbdd.commit()

  def getall(self):
    self.cursor.execute("""select * from links where lock<4""")
    return self.cursor.fetchall()

  def addlink(self, link):
    # Normalize URL
    link = re.sub("/es/","/",link)
    self.cursor.execute("""insert into links values('%s','',0,3)""" % link)
    self.bbdd.commit()

  def updatelinkname(self, link, name):
   self.cursor.execute("""update links set filename='%s' where link='%s'""" % (name, link) )
   self.bbdd.commit()

  def removelink(self, link):
    self.cursor.execute("""delete from links where link='%s'""" % link)
    self.bbdd.commit()

  def delete_historic(self):
    self.cursor.execute("""delete from links where lock=4""")
    self.bbdd.commit()

  def downloaddone(self, link):
    # When link is downloaded, set lock=4
    self.cursor.execute("""update links set lock=4 where link='%s'""" % link)
    self.bbdd.commit()

  def gethistoric(self):
    # Return all links downloaded
    self.cursor.execute("""select * from links where lock=4""")
    return self.cursor.fetchall()

  def checklink(self, link):
    self.cursor.execute("""select * from links where link='%s'""" % link)
    return self.cursor.fetchone()

  def catchlink(self):
    self.cursor.execute("""select * from links where lock=0""")
    return self.cursor.fetchone()

  def removefailed(self):
    self.cursor.execute("""delete from links where lock='2'""")
    self.bbdd.commit()

  def insertlog(self, text):
    self.cursor.execute("""insert into logs values (DATETIME('NOW'),'%s')""" % text)
    self.bbdd.commit()

  def locklink(self, link):
    self.cursor.execute("""update links set lock='1' where link='%s'""" % link)
    self.bbdd.commit()

  def unlocklink(self, link):
    self.cursor.execute("""update links set lock='0' where link='%s'""" % link)
    self.bbdd.commit()

  def pauselink(self, link):
    self.cursor.execute("""update links set lock='3' where link='%s'""" % link)
    self.bbdd.commit()

  def getparameter(self, name):
    self.cursor.execute("""select value from config where parameter='%s'""" % name)
    return self.cursor.fetchone()[0]

  def setparameter(self, param, value):
    self.cursor.execute("""update config set value='%s' where parameter='%s'""" % (value,param))
    self.bbdd.commit()

  def failed(self, link):
    self.cursor.execute("""update links set lock='2' where link='%s'""" % link)
    self.bbdd.commit()

  def resetstatus(self):
    self.cursor.execute("""update links set lock='0' where lock='1'""")
    self.bbdd.commit()

  def close(self):
    self.cursor.close()
    self.bbdd.close()

